﻿-- =============================================
-- Author:		<Author,XFL>
-- Create date: <Create Date,20111230>
-- Description:	<Description,--获取固定补助 高级搜索>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Employee_FixedAllowance_GetList]
(
	@CompanyId  nvarchar(500),
	@EName nvarchar(50),
	@StartIndex int,
	@EndIndex int
)
AS
BEGIN

	--	将公司ID串 插入临时表中
		declare @Companyid_T table(id int identity(1,1),cid int)
		declare @CompanyID_S nvarchar(500)
		set @CompanyID_S=@CompanyId+','
		print @CompanyID_S
		Declare @i int
		Set @i=0
		while(@i<len(@CompanyID_S))
		begin 
		Insert Into @Companyid_T(cid) Values(substring(@CompanyID_S,@i,charindex(',',@CompanyID_S,@i)-@i))
		Set @i=charindex(',',@CompanyID_S,@i)+1
		end
		
	--	select * from Company  where id in (select cid from @Companyid_T)
					

		;WITH list As(
		Select ROW_NUMBER() OVER 
		(
		ORDER BY Amount desc
		)
		AS Row,
		 Id
		,CompanyId
		,(Select c.CompanyName From Company c Where c.Id=CompanyId) as CompanyName
		,DepartmentId
		,(Select d.DName From Department d Where d.Id=DepartmentId) as DepartmentName
		,UserName
		,EName
		,Amount
		,Memo
		,CreateDate
		From 
			Employee_FixedAllowance 
		Where
			(@CompanyId='0' or CompanyId in (select C.cid from @Companyid_T C))
			and (@EName Is Null Or EName like '%'+@EName+'%')
		)
		Select *,(select count(0) from list ) AS Sum_Count  From list Where Row between @StartIndex and @EndIndex 
END
